package com.situ.blog.dao.impl;

import com.situ.blog.dao.IBlogDao;
import com.situ.blog.pojo.entity.Blog;
import com.situ.blog.pojo.query.BlogQuery;
import com.situ.blog.pojo.vo.BlogVO;
import com.situ.blog.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.List;


public class BlogDaoImpl implements IBlogDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public List<BlogVO> selectByPage(BlogQuery blogQuery) {
        //
        String sql = "SELECT b.id,b.`name`,b.image,b.click_count,b.content,b.`status`,b.create_time,b.update_time,t.id AS typeId,t.`name` AS typeName\n" +
                "FROM blog AS b INNER JOIN blog_type AS t \n" +
                "ON b.type_id=t.id where 1=1";

        List<Object> queryList = new ArrayList<>();

        String queryName = blogQuery.getName();
        if (queryName != null && !queryName.equals("")){
            sql += " and b.name like ?";
            queryList.add("%" + queryName + "%");
        }


        if (!ObjectUtils.isEmpty(blogQuery.getTypeId())) {
            sql += " and b.type_id=?";
            queryList.add(blogQuery.getTypeId());
        }
        if (!ObjectUtils.isEmpty(blogQuery.getBeginCreateTime()) &&
                !ObjectUtils.isEmpty(blogQuery.getEndCreateTime())) {
            sql += " and b.create_time between ? and ?";
            queryList.add(blogQuery.getBeginCreateTime());
            queryList.add(blogQuery.getEndCreateTime());
        }

        sql += " limit ?,?";
        int offset = (blogQuery.getPage() - 1)*blogQuery.getLimit();
        queryList.add(offset);
        queryList.add(blogQuery.getLimit());
        //queryList是集合，queryList.toArray是数组
        List<BlogVO> list = template.query(sql,
                new BeanPropertyRowMapper<>(BlogVO.class),
                queryList.toArray());
        return list;
    }

    @Override
    public int selectTotalCount(BlogQuery blogQuery) {
        String sql = "SELECT count(*)\n" +
                "FROM blog AS b INNER JOIN blog_type AS t \n" +
                "ON b.type_id=t.id where 1=1";
        List<Object> queryList = new ArrayList<>();
        String queryName = blogQuery.getName();
        if (queryName != null && !queryName.equals("")){
            sql += " and b.name like ?";
            queryList.add("%"+ queryName +"%");
        }

        if (!ObjectUtils.isEmpty(blogQuery.getTypeId())) {
            sql += " and b.type_id=?";
            queryList.add(blogQuery.getTypeId());
        }
        if (!ObjectUtils.isEmpty(blogQuery.getBeginCreateTime()) &&
                !ObjectUtils.isEmpty(blogQuery.getEndCreateTime())) {
            sql += " and b.create_time between ? and ?";
            queryList.add(blogQuery.getBeginCreateTime());
            queryList.add(blogQuery.getEndCreateTime());
        }

        //Integer count = template.queryForObject(sql, Integer.class, queryList.toArray());
        //return count;

        return template.queryForObject(sql, Integer.class, queryList.toArray());
    }

    @Override
    public void deleteById(int id) {
        template.update("delete from blog where id=?",id);
    }

    @Override
    public void add(Blog blog) {
        template.update("insert into blog(name,type_id,content,image) values (?,?,?,?)",
                blog.getName(),blog.getTypeId(),blog.getContent(),blog.getImage());
    }

    @Override
    public Blog selectById(int id) {
        return template.queryForObject("select * from blog where id=?",
                new BeanPropertyRowMapper<>(Blog.class),id);
    }

    @Override
    public void update(Blog blog) {
        template.update("update blog set name=?,content=?,type_id=?,image=? where id=?",
                blog.getName(),blog.getContent(),blog.getTypeId(),blog.getImage(),blog.getId());
    }

    @Override
    public void updateStatus(int id, int status) {
        template.update("update blog set status=? where id=?",status,id);
    }

    @Override
    public List<BlogVO> selectBlogCount(BlogQuery blogQuery) { //饼状图
        String sql = "SELECT t.`name` ,count(*) as value\n" +
                "FROM blog AS b INNER JOIN blog_type AS t \n" +
                "ON b.type_id=t.id";

        /*return template.queryForObject(sql,Integer.class,);*/
        return null;
    }

    @Override
    public List<BlogVO> selectAll() {
        String sql = "SELECT b.id,b.`name`,b.image,b.click_count,b.content,b.`status`,b.create_time,b.update_time,t.id AS typeId,t.`name` AS typeName\n" +
                "FROM blog AS b INNER JOIN blog_type AS t \n" +
                "ON b.type_id=t.id";
        //无参，后面不用写
        return template.query(sql,new BeanPropertyRowMapper<>(BlogVO.class));
    }


    //上传文件
    @Override
    public void addExcel(BlogVO blogVO) {
        template.update("insert into blog(id,name,image,click_count,type_id,status,is_deleted,create_time,update_time) values(?,?,?,?,?,?,?,?,?)",
                blogVO.getId(), blogVO.getName(), blogVO.getImage(),blogVO.getClickCount(),blogVO.getTypeId(),blogVO.getStatus(),blogVO.getDeleted(),blogVO.getCreateTime(), blogVO.getUpdateTime());
    }

}
